﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class StoreObject
    {
        public const string sql = @"
SELECT '' as Итоги, '' as Склад, '' as Груз, '' as Проект, 'Вес*КолВо' as Вес 
UNION ALL
SELECT 'Поступило на склад:' as Итоги, '' as Склад, '' as Груз, '' as Проект, '' as Вес 
UNION ALL
SELECT 	'' as Итоги,
		DictionaryPlaces.Name as Склад, 
		DictionaryGoods.Name as Груз, 
		DictionaryProject.Name as Проект,
		convert(nchar(20),sum(w.Weight * COALESCE(w.[CountWork],0))) as Вес 
FROM Work as w
LEFT JOIN DictionaryPlaces on w.UnLoadingPlace = DictionaryPlaces.ID
LEFT JOIN DictionaryGoods on w.Goods = DictionaryGoods.ID
LEFT JOIN DictionaryProject on w.Project = DictionaryProject.ID
WHERE w.UnLoadingPlace in (select id from DictionaryPlaces where Store = 1)
GROUP BY DictionaryPlaces.Name, DictionaryGoods.Name, DictionaryProject.Name
UNION ALL
SELECT 'Отпушено со склада:' as Итоги, '' as Склад, '' as Груз, '' as Проект, '' as Вес 
UNION ALL
SELECT 	'' as Итоги,
		DictionaryPlaces.Name as Склад, 
		DictionaryGoods.Name as Груз, 
		DictionaryProject.Name as Проект,
		convert(nchar(20),sum(w.Weight * COALESCE(w.[CountWork],0))) as Вес 
FROM Work as w
LEFT JOIN DictionaryPlaces on w.LoadingPlace = DictionaryPlaces.ID
LEFT JOIN DictionaryGoods on w.Goods = DictionaryGoods.ID
LEFT JOIN DictionaryProject on w.Project = DictionaryProject.ID
WHERE w.LoadingPlace in (select id from DictionaryPlaces where Store = 1)
GROUP BY DictionaryPlaces.Name, DictionaryGoods.Name, DictionaryProject.Name
UNION ALL
SELECT 'Остаток по складу:' as Итоги, '' as Склад, '' as Груз, '' as Проект, '' as Вес 
UNION ALL
SELECT '' as Итоги, TOTAL.Склад as Склад, TOTAL.Груз as Груз, TOTAL.Проект as Проект, convert(nchar(20),sum(TOTAL.Weight)) as Вес FROM
(SELECT 	'' as Итоги,
		DictionaryPlaces.Name as Склад, 
		DictionaryGoods.Name as Груз, 
		DictionaryProject.Name as Проект,
		sum(w.Weight * COALESCE(w.[CountWork],0)) as Weight
FROM Work as w
LEFT JOIN DictionaryPlaces on w.UnLoadingPlace = DictionaryPlaces.ID
LEFT JOIN DictionaryGoods on w.Goods = DictionaryGoods.ID
LEFT JOIN DictionaryProject on w.Project = DictionaryProject.ID
WHERE w.UnLoadingPlace in (select id from DictionaryPlaces where Store = 1)
GROUP BY DictionaryPlaces.Name, DictionaryGoods.Name, DictionaryProject.Name
union all
SELECT 	'' as Итоги,
		DictionaryPlaces.Name as Склад, 
		DictionaryGoods.Name as Груз, 
		DictionaryProject.Name as Проект,
		-sum(w.Weight * COALESCE(w.[CountWork],0)) as Weight
FROM Work as w
LEFT JOIN DictionaryPlaces on w.LoadingPlace = DictionaryPlaces.ID
LEFT JOIN DictionaryGoods on w.Goods = DictionaryGoods.ID
LEFT JOIN DictionaryProject on w.Project = DictionaryProject.ID
WHERE w.LoadingPlace in (select id from DictionaryPlaces where Store = 1)
GROUP BY DictionaryPlaces.Name, DictionaryGoods.Name, DictionaryProject.Name) as TOTAL
GROUP BY TOTAL.Склад, TOTAL.Груз, TOTAL.Проект
";
        public string Итоги { get; set; }
        public string Склад { get; set; }
        public string Груз { get; set; }
        public string Проект { get; set; }
        public string Вес { get; set; }
    }
}
